package com.itany.corejava.code13_jdbc;

/**
 * @author MissChen
 * @version 1.0
 * @date 2023 年12月13日 16:21
 */


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 两张表
 * 班级表：主键自增长、班级号(唯一不为空)、所属专业
 * id、no、major
 * 学生表:主键自增长、学号、姓名、年龄、所属班级(外键)
 * id、no、name、age、class_id
 * 1.插入班级以及该班级中的所有学生信息,有2个参数(关于班级,关于学生的集合)
 * 2.根据id查询指定班级以及该班级中所有的学生信息，有1个参数
 * 3.查询所有学生以及其所属班级信息
 */
public class Test13 {

    public static void main(String[] args) {
        ClassBean classBean = new ClassBean("wbs23091", "java");
        List<Student> students = new ArrayList<>();
        Student stu1 = new Student("1001", "alice", 20);
        Student stu2 = new Student("1002", "tom", 21);
        Student stu3 = new Student("1003", "cici", 20);
        students.add(stu1);
        students.add(stu2);
        students.add(stu3);
        try {
            // insertClassAndStu(classBean,students);
            // System.out.println("添加成功");

            // ClassBean c = queryClassDetailById(1);
            // System.out.println(c);

            List<Student> list = queryAllStudent();
           for(Student stu:list){
               System.out.println(stu);
           }
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
    }

    /**
     * 先插入班级，再将指定学生的集合插入到学生表
     *
     * @param classBean
     * @param students
     * @throws DataAccessException
     */
    public static void insertClassAndStu(ClassBean classBean, List<Student> students) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Integer id = null;
        try {
            conn = JDBCUtil.getConnection();
            conn.setAutoCommit(false);
            String sql = new StringBuffer()
                    .append(" insert into ")
                    .append(" t_class ")
                    .append(" (no,major) ")
                    .append(" values ")
                    .append(" (?,?) ")
                    .toString();
            ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1, classBean.getNo());
            ps.setString(2, classBean.getMajor());
            ps.executeUpdate();

            rs = ps.getGeneratedKeys();
            while (rs.next()) {
                id = rs.getInt(1);
            }
            classBean.setId(id);
            sql = new StringBuffer()
                    .append(" insert into ")
                    .append(" t_student ")
                    .append(" (no,name,age,class_id) ")
                    .append(" values ")
                    .append(" (?,?,?,?) ")
                    .toString();
            ps = conn.prepareStatement(sql);
            for (Student student : students) {
                student.setClassBean(classBean);
                ps.setString(1, student.getNo());
                ps.setString(2, student.getName());
                ps.setInt(3, student.getAge());
                ps.setInt(4, student.getClassBean().getId());
                ps.executeUpdate();
            }
            conn.commit();


        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
                throw new DataAccessException("回滚事务失败");
            }
            throw new DataAccessException("访问数据库失败");
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }

    }


    /**
     * 根据班级的id查询班级以及班级中学生的信息
     *
     * @param id
     * @return
     * @throws DataAccessException
     */
    public static ClassBean queryClassDetailById(Integer id) throws DataAccessException {
        ClassBean classBean = null;

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtil.getConnection();

            String sql = new StringBuffer()
                    .append(" select c.id,c.no,c.major,s.id,s.no,s.name,s.age ")
                    .append(" from t_class c  ")
                    .append(" left join t_student s  ")
                    .append(" on c.id=s.class_id ")
                    .append(" where c.id=? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                if (classBean == null) {
                    classBean = new ClassBean();
                    classBean.setId(rs.getInt("c.id"));
                    classBean.setNo(rs.getString("c.no"));
                    classBean.setMajor(rs.getString("c.major"));
                }
                Student student = new Student();
                student.setId(rs.getInt("s.id"));
                student.setNo(rs.getString("s.no"));
                student.setName(rs.getString("s.name"));
                student.setAge(rs.getInt("s.age"));
                classBean.addStudent(student);
            }


        } catch (DataAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }


        return classBean;


    }

    /**
     * 查询所有学生以及学生所属的班级信息
     *
     * @return
     * @throws DataAccessException
     */
    public static List<Student> queryAllStudent() throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            conn = JDBCUtil.getConnection();

            String sql = new StringBuffer()
                    .append(" select s.id,s.no,s.name,s.age,c.id,c.no,c.major ")
                    .append(" from t_student s  ")
                    .append(" left join t_class c  ")
                    .append(" on c.id=s.class_id ")
                    .toString();
            ps=conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {

                Student student = new Student();
                student.setId(rs.getInt("s.id"));
                student.setNo(rs.getString("s.no"));
                student.setName(rs.getString("s.name"));
                student.setAge(rs.getInt("s.age"));
                ClassBean classBean = new ClassBean();
                classBean.setId(rs.getInt("c.id"));
                classBean.setNo(rs.getString("c.no"));
                classBean.setMajor(rs.getString("c.major"));

                student.setClassBean(classBean);

                students.add(student);

            }


        } catch (DataAccessException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
        return students;

    }

}
